/***********************************************************************************************************************************
 *   	Name: Audit_Read.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: 
**/
USE TempDB;
CREATE TABLE #audit_file (
	event_time						DATETIME2(7)	NOT	Null,
	sequence_number					INT				NOT	Null,
	action_id						VARCHAR(4)			Null,
	succeeded						BIT				NOT	Null,
	permission_bitmask				BIGINT			NOT	Null,
	is_column_permission			BIT				NOT	Null,
	session_id						SMALLINT		NOT	Null,
	server_principal_id				INT				NOT	Null,
	database_principal_id			INT				NOT	Null,
	target_server_principal_id		INT				NOT	Null,
	target_database_principal_id	INT				NOT	Null,
	object_id						INT				NOT	Null,
	class_type						VARCHAR(2)			Null,
	session_server_principal_name	NVARCHAR(128)		Null,
	server_principal_name			NVARCHAR(128)		Null,
	server_principal_sid			VARBINARY(85)		Null,
	database_principal_name			NVARCHAR(128)		Null,
	target_server_principal_name	NVARCHAR(128)		Null,
	target_server_principal_sid		VARBINARY(85)		Null,
	target_database_principal_name	NVARCHAR(128)		Null,
	server_instance_name			NVARCHAR(128)		Null,
	database_name					NVARCHAR(128)		Null,
	schema_name						NVARCHAR(128)		Null,
	object_name						NVARCHAR(128)		Null,
	statement						NVARCHAR(4000)		Null,
	additional_information			NVARCHAR(4000)		Null,
	file_name						NVARCHAR(260)	NOT	Null,
	audit_file_offset				BIGINT			NOT	Null);
DECLARE @file_pattern NVARCHAR(255)= master.DBAdmin.GetInstancePath('ErrorDumpDir') + N'Audit-Login_*.SQLAudit';
INSERT INTO #audit_file (	event_time,sequence_number,action_id,succeeded,permission_bitmask,is_column_permission,session_id,server_principal_id,database_principal_id,target_server_principal_id,target_database_principal_id,object_id,class_type,session_server_principal_name,server_principal_name,server_principal_sid,database_principal_name,target_server_principal_name,target_server_principal_sid,target_database_principal_name,server_instance_name,database_name,schema_name,object_name,statement,additional_information,file_name,audit_file_offset)
	SELECT					event_time,sequence_number,action_id,succeeded,permission_bitmask,is_column_permission,session_id,server_principal_id,database_principal_id,target_server_principal_id,target_database_principal_id,object_id,class_type,session_server_principal_name,server_principal_name,server_principal_sid,database_principal_name,target_server_principal_name,target_server_principal_sid,target_database_principal_name,server_instance_name,database_name,schema_name,object_name,statement,additional_information,file_name,audit_file_offset
	FROM sys.fn_get_audit_file(@file_pattern, DEFAULT, DEFAULT);
GO

CREATE TABLE #logininfo (
	account_name		SYSNAME	NOT Null,
	type				CHAR(8)	NOT Null,
	privilege			CHAR(9)	NOT Null,
	mapped_login_name	SYSNAME	NOT Null,
	permission_path		SYSNAME		Null)
 

SELECT DISTINCT N'INSERT INTO #logininfo EXECUTE sys.xp_logininfo ''' + server_principal_name + N''', ''all'';' FROM #audit_file WHERE server_principal_name LIKE N'%\%';
SELECT DISTINCT COALESCE(permission_path, account_name) FROM #logininfo ORDER BY 1;

